package su.storage.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import net.kuakao.core.dto.PagerDTO;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import su.base.Constants;
import su.base.dao.SuBaseDAO;
import su.storage.dao.IStorageMaterialDAO;
import su.storage.entity.StorageMaterial;


@Repository
public class StorageMaterialDAO extends SuBaseDAO implements IStorageMaterialDAO {
	
	
	public List<StorageMaterial> queryByPage(PagerDTO s) throws SQLException {
		String sql = "select sm.*,pm.mname,pm.totalnum from su_storage_material sm,su_product_material pm where sm.materialid=pm.id and sm.status=1";
		if(StringUtils.isNotBlank(s.getS1())){//原料id
			sql += " and sm.materialid="+s.getS1();
		}
		if(StringUtils.isNotBlank(s.getS2())){//原料名称
			sql += " and pm.mname like '%"+s.getS2()+"%'";
		}
		if(StringUtils.isNotBlank(s.getS3())){//出入类型
			sql += " and sm.flowtype="+s.getS3();
		}
		if(StringUtils.isNotBlank(s.getSt1())){//时间从
			sql += " and sm.flowtime>'"+s.getSt1()+"'";
		}
		if(StringUtils.isNotBlank(s.getEt1())){//时间到
			sql += " and sm.flowtime<'"+s.getEt1()+" 23:59:59'";
		}
		
		sql += " ORDER BY sm.flowtime desc";
		
		return super.queryByPage(sql, new RowMapper<StorageMaterial>() {

			public StorageMaterial mapRow(ResultSet rs, int arg1) throws SQLException {
				StorageMaterial entity = new StorageMaterial();
				entity.setId(rs.getInt("id"));
				entity.setStorage(rs.getInt("storage"));
				entity.setMaterialid(rs.getInt("materialid"));
				entity.setFlowtime(rs.getTimestamp("flowtime"));
				entity.setFlowtype(rs.getInt("flowtype"));
				entity.setFlownum(rs.getDouble("flownum"));
				entity.setTotal(rs.getDouble("total"));
				entity.setGhsid(rs.getInt("ghsid"));
				entity.setPrice(rs.getDouble("price"));
				entity.setSdesc(rs.getString("sdesc"));
				entity.setStatus(rs.getInt("status"));
				entity.setOuttype(rs.getInt("outtype"));
				entity.setMname(rs.getString("mname"));
				entity.setMtotal(rs.getDouble("totalnum"));
				return entity;
			}
			
		}, s);
	}
	
	
	public StorageMaterial getById(int id) throws SQLException {
		String sql = "select sm.*,pm.mname,pm.totalnum from su_storage_material sm,su_product_material pm where sm.materialid=pm.id and sm.status=1 and sm.id=? limit 1";
		return super.queryForObject(sql, new Object[]{id}, new RowMapper<StorageMaterial>() {
			public StorageMaterial mapRow(ResultSet rs, int value) throws SQLException {
				StorageMaterial entity = new StorageMaterial();
				entity.setId(rs.getInt("id"));
				entity.setStorage(rs.getInt("storage"));
				entity.setMaterialid(rs.getInt("materialid"));
				entity.setFlowtime(rs.getTimestamp("flowtime"));
				entity.setFlowtype(rs.getInt("flowtype"));
				entity.setFlownum(rs.getDouble("flownum"));
				entity.setTotal(rs.getDouble("total"));
				entity.setGhsid(rs.getInt("ghsid"));
				entity.setPrice(rs.getDouble("price"));
				entity.setSdesc(rs.getString("sdesc"));
				entity.setStatus(rs.getInt("status"));
				entity.setOuttype(rs.getInt("outtype"));
				entity.setCreateTime(rs.getTimestamp("create_time"));
				entity.setMname(rs.getString("mname"));
				entity.setMtotal(rs.getDouble("totalnum"));
				return entity;
			}
		});
	}

	
	public void remove(int id) throws SQLException {
		final String sql = "update su_storage_material set status=? where id=?";
		super.update(sql, new Object[]{Constants.STATUS_DISABLE,id});
	}
	
	
	

	public void insert(StorageMaterial o) throws SQLException {
		String sql = "insert into su_storage_material(storage ,materialid ,flowtime ,flowtype ,flownum ,total ,ghsid ,price ,sdesc ,status ,outtype ,create_time ) values(? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? )";
		super.update(sql, new Object[]{o.getStorage() ,o.getMaterialid() ,o.getFlowtime() ,o.getFlowtype() ,o.getFlownum() ,o.getTotal() ,o.getGhsid() ,o.getPrice() ,o.getSdesc() ,o.getStatus() ,o.getOuttype() ,o.getCreateTime() });
	}
	
	
	
	public void update(StorageMaterial o) throws SQLException {
		StringBuffer updateSql = new StringBuffer("update su_storage_material set ");
		List<Object> args = new ArrayList<Object>();
		args.add(o.getStorage());
		updateSql.append("storage=?").append(",");
		//args.add(o.getMaterialid());
		//updateSql.append("materialid=?").append(",");
		args.add(o.getFlowtime());
		updateSql.append("flowtime=?").append(",");
		args.add(o.getFlowtype());
		updateSql.append("flowtype=?").append(",");
		args.add(o.getFlownum());
		updateSql.append("flownum=?").append(",");
		args.add(o.getTotal());
		updateSql.append("total=?").append(",");
		args.add(o.getGhsid());
		updateSql.append("ghsid=?").append(",");
		args.add(o.getPrice());
		updateSql.append("price=?").append(",");
		args.add(o.getSdesc());
		updateSql.append("sdesc=?").append(",");
			
		
		args.add(o.getId());
			
		
		String sql = updateSql.substring(0, updateSql.length()-1) + " where id = ? "; 
		super.update(sql, args.toArray());
	}

	public void updateTotalAfter(int materialid,double reduce,Date afterDate)throws Exception{
		final String sql = "update su_storage_material set total=total-? where materialid=? and create_time>?";
		super.update(sql, new Object[]{reduce,materialid,afterDate});
	}
}

